﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Netnr.CodeBuilder.Core
{
    class DbHelperSQL
    {

        #region 数据库连接字符串
        public static string sqlConn = string.Empty;
        #endregion


        #region 查询 数据 （返回 DataSet DataTable DataReader）

        /// <summary>
        /// 查询数据集 返回DataSet
        /// </summary>
        /// <param name="cmdTxt">SQL语句</param>
        /// <param name="cmdParams">SQL参数组</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string cmdTxt, SqlParameter[] cmdParams = null)
        {
            using (SqlConnection conn = new SqlConnection(sqlConn))
            {
                SqlDataAdapter sda = new SqlDataAdapter(cmdTxt, conn);
                if (cmdParams != null)
                {
                    foreach (SqlParameter p in cmdParams)
                    {
                        sda.SelectCommand.Parameters.Add(p);
                    }
                }
                DataSet ds = new DataSet();
                sda.Fill(ds);
                return ds;
            }
        }

        /// <summary>
        /// 查询数据集 返回DataTable
        /// </summary>
        /// <param name="cmdTxt">SQL语句</param>
        /// <param name="cmdParams">SQL参数组</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string cmdTxt, SqlParameter[] cmdParams = null)
        {
            return GetDataSet(cmdTxt, cmdParams).Tables[0];
        }

        /// <summary>
        /// 查询返回 DataReader
        /// </summary>
        /// <param name="cmdTxt">SQL语句</param>
        /// <param name="cmdParams">SQL参数组</param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string cmdTxt, SqlParameter[] cmdParams = null)
        {
            using (SqlConnection conn = new SqlConnection(sqlConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(cmdTxt, conn);
                if (cmdParams != null)
                {
                    foreach (SqlParameter p in cmdParams)
                    {
                        cmd.Parameters.Add(p);
                    }
                }

                SqlDataReader Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                conn.Close();
                return Reader;
            }
        }


        /// <summary>
        /// 返回第一行第一列的值 类型为 object
        /// </summary>
        /// <param name="cmdTxt">SQL命令</param>
        /// <param name="cmdParams">SQL参数组</param>
        /// <returns>返回第一行第一列的值 object </returns>
        public static object GetScalar(string cmdTxt, SqlParameter[] cmdParams = null)
        {
            using (SqlConnection conn = new SqlConnection(sqlConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(cmdTxt, conn);
                if (cmdParams != null)
                {
                    foreach (SqlParameter p in cmdParams)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return obj;
            }
        }

        #endregion


        #region 增、删、改

        /// <summary>
        /// 执行 SQL 语句 返回受影响行数
        /// </summary>
        /// <param name="cmdTxt">SQL语句</param>
        /// <param name="cmdParams">SQL 参数组</param>
        public static int ExecSql(string cmdTxt, SqlParameter[] cmdParams = null)
        {
            using (SqlConnection conn = new SqlConnection(sqlConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(cmdTxt, conn);
                if (cmdParams != null)
                {
                    foreach (SqlParameter p in cmdParams)
                    {
                        cmd.Parameters.Add(p);
                    }
                }
                int count = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
                return count;
            }
        }

        #endregion
    }
}
